﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[esf_files].[P_QueryFileReferencesByWordContents]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [esf_files].[P_QueryFileReferencesByWordContents]
GO

-- esf_files.P_QueryFileReferencesByWordContents @pWordList = 'decreto,1011'
-- esf_files.P_QueryFileReferencesByWordContents @pWordList = 'pago,por,evento'
-- esf_files.P_QueryFileReferencesByWordContents @pWordList = 'negociacion,20109'
-- esf_files.P_QueryFileReferencesByWordContents @pWordList = 'negociacion,2008'
-- esf_files.P_QueryFileReferencesByWordContents @pWordList = 'arturo,carlos,cali'
-- esf_files.P_QueryFileReferencesByWordContents @pWordList = 'modalidad,capitacion'
-- esf_files.P_QueryFileReferencesByWordContents @pWordList = 'moda,capitac'
-- esf_files.P_QueryFileReferencesByWordContents @pWordList = 'bogota'
-- esf_files.P_QueryFileReferencesByWordContents @pWordList = 'xxxxxxxxxxxxxx'
-- esf_files.P_QueryFileReferencesByWordContents @pWordList = 'myrey'
-- esf_files.P_QueryFileReferencesByWordContents @pWordList = 'envigado'
-- esf_files.P_QueryFileReferencesByWordContents @pWordList = '900215983'
-- esf_files.P_QueryFileReferencesByWordContents @pWordList = '900206215'
-- esf_files.P_QueryFileReferencesByWordContents @pWordList = '860035992'
-- esf_files.P_QueryFileReferencesByWordContents @pMonitoredDirectorySubCategoryID = 3002, @pWordList = '900206215'
-- esf_files.P_QueryFileReferencesByWordContents @pXMLMultiLevelsSearch = '<FRLs><FRL Level="1" Name="SANTAMARTA" /></FRLs>'
CREATE PROCEDURE esf_files.P_QueryFileReferencesByWordContents
	@pURL VARCHAR (MAX) = NULL
	,@pLastUpdateAction VARCHAR (MAX) = NULL
	,@pIsEnabled BIT = 1
	,@pMonitoredDirectorySubCategoryID INT = NULL
	,@pFileTypeID INT = NULL
	,@pMonitoredDirectoryID INT = NULL
	,@pCreationDate DATETIME = NULL
	,@pCreationDateTimeStart DATETIME = NULL
	,@pCreationDateTimeEnd DATETIME = NULL
	,@pLastWriteDate DATETIME = NULL
	,@pLastWriteDateTimeStart DATETIME = NULL
	,@pLastWriteDateTimeEnd DATETIME = NULL
	,@pOnlyName VARCHAR (MAX) = NULL
	,@pEnterpriseIdentificationNumber VARCHAR (MAX) = NULL
	,@pEnterpriseName VARCHAR (MAX) = NULL

	,@pMultiOnlyName VARCHAR (MAX) = NULL

	,@pXMLMultiLevelsSearch XML = NULL
	
	,@pListSeparator CHAR (1) = ','
	
	,@pWordList	VARCHAR (MAX) = NULL
	
	,@pPageIndex			BIGINT = 0
	,@pPageSize				BIGINT = 10
AS

	SET NOCOUNT ON
	

	DECLARE @words TABLE (ID INT, name VARCHAR (500), searchItem VARCHAR (500))
	
	DECLARE @searchWords TABLE (searchItem VARCHAR (500))
	
	DECLARE @fileReferenceID INT
	DECLARE @wordListID INT
	DECLARE @wordNameFound VARCHAR (500)
	DECLARE @wordSearchName VARCHAR (500)
	
	DECLARE @XMLMultiLevelsSearch XML
	DECLARE @XMLMultiLevelsSearchCount INT
	
	DECLARE @XMLMultiLevelsSearchTable TABLE (Level INT, Name VARCHAR (MAX))
	
	
	DECLARE @hits TABLE (
		FileReferenceID INT, WordListID INT, 
		WordNameFound VARCHAR (500), WordSearchName VARCHAR (500), 
		PageIndex INT,
		X0 INT, Y0 INT, 
		X1 INT, Y1 INT)
		
	
	IF @pWordList IS NOT NULL
	BEGIN
	
		INSERT INTO @searchWords
		SELECT tempItem FROM dbo.FUBSplitString (@pWordList, ',')
			
			
		INSERT INTO @words 
		SELECT 
			DISTINCT W.ID, W.Name, WL.searchItem
		FROM 
			@searchWords WL
		INNER JOIN
			esf_sso.VWord W WITH (NOLOCK) ON 
				W.Name LIKE WL.searchItem + '%' 
				--OR dbo.NeedlemanWunch (W.Name, WL.searchItem) > 0.9

		--SELECT * from @searchWords
		--SELECT * from @words
		
		
		-- Se verifica cada referencia de archivo que contiene al menos una de las palabras
		-- para identificarlos y organizarlos por quienes contengan mas palabras
		-- Por ejemplo: si contiene 2 y otro 1 el de 2 va de primero
		INSERT INTO @hits
		SELECT
			DISTINCT 
			FROCR.FileReferenceID
			,WL.ID				WordListID
			,WL.Name			WordNameFound
			,WL.searchItem		WordSearchName
			,FROCR.PageIndex	WordNameFoundPageIndex
			
			,FROCR.X0
			,FROCR.Y0
			,FROCR.X1
			,FROCR.Y1
		FROM
			esf_files.FileReferenceWordHit FROCR WITH (NOLOCK)
		INNER JOIN
			esf_files.FileReference FR WITH (NOLOCK) ON 
				FR.ID = FROCR.FileReferenceID
		INNER JOIN
			@words WL ON 
				FROCR.WordID = WL.ID
		WHERE 
			FR.IsEnabled = 1
		ORDER BY
			FROCR.FileReferenceID
			,WL.searchItem
		
		--SELECT * FROM @hits	
			
	END
		
	IF @pXMLMultiLevelsSearch IS NOT NULL
	BEGIN
		
		INSERT INTO @XMLMultiLevelsSearchTable
		SELECT
			MLS.F.value ('@Level', 'int') AS Level
			,MLS.F.value ('@Name', 'varchar (MAX)') AS Name
		FROM
			@pXMLMultiLevelsSearch.nodes ('/FRLs/child::node ()') MLS (F)

		SET @XMLMultiLevelsSearchCount = (SELECT COUNT (Level) FROM @XMLMultiLevelsSearchTable)
	
	END


	DECLARE @PageVectorTable TABLE (Ri INT, RCount INT, ParentFileReferenceID INT)
		
		
	INSERT INTO @PageVectorTable
	SELECT * FROM
		(SELECT
			ROW_NUMBER () OVER (ORDER BY X.ParentFileReferenceID DESC) AS Ri
			,COUNT (*) OVER () RCount
			,X.ParentFileReferenceID
		FROM
			(SELECT
				DISTINCT 
				ISNULL (FR.ParentFileReferenceID, FR.ID) ParentFileReferenceID
			FROM 
				esf_files.FileReference FR WITH (NOLOCK)
			LEFT JOIN
				@hits H ON H.FileReferenceID = FR.ID
			LEFT JOIN
				esf_files.FileReferenceLevel FRL ON FRL.FileReferenceID = FR.ID
			LEFT JOIN
				dbo.FUBSplitString (@pMultiOnlyName, @pListSeparator) MON 
					ON FR.OnlyName LIKE MON.tempItem
			LEFT JOIN
				esf_sso.VEnterprise E ON E.Ent_ID = FR.EnterpriseID
			
			WHERE 
				(@pURL IS NULL OR FR.URL LIKE @pURL)
				AND (@pLastUpdateAction IS NULL OR FR.LastUpdateAction LIKE @pLastUpdateAction)
				AND (@pIsEnabled IS NULL OR FR.IsEnabled = @pIsEnabled)
				AND (@pMonitoredDirectorySubCategoryID IS NULL OR FR.MonitoredDirectorySubCategoryID = @pMonitoredDirectorySubCategoryID)
				AND (@pFileTypeID IS NULL OR FR.FileTypeID = @pFileTypeID)
				AND (@pOnlyName IS NULL OR FR.OnlyName LIKE @pOnlyName)
				AND (@pMonitoredDirectoryID IS NULL OR FR.MonitoredDirectoryID = @pMonitoredDirectoryID)
				AND (@pCreationDate IS NULL OR CONVERT (VARCHAR (8), FR.CreationDateTime, 112) = CONVERT (VARCHAR (8), @pCreationDate))
				AND (@pCreationDateTimeStart IS NULL OR FR.CreationDateTime >= @pCreationDateTimeStart)
				AND (@pCreationDateTimeEnd IS NULL OR FR.CreationDateTime <= @pCreationDateTimeEnd)
				AND (@pLastWriteDate IS NULL OR CONVERT (VARCHAR (8), FR.LastWriteDateTime, 112) = CONVERT (VARCHAR (8), @pLastWriteDate))
				AND (@pLastWriteDateTimeStart IS NULL OR FR.LastWriteDateTime >= @pLastWriteDateTimeStart)
				AND (@pLastWriteDateTimeEnd IS NULL OR FR.LastWriteDateTime <= @pLastWriteDateTimeEnd)
				
				AND (@pEnterpriseIdentificationNumber IS NULL OR E.Ent_IdentificationNumber LIKE @pEnterpriseIdentificationNumber)
				AND (@pEnterpriseName IS NULL OR E.Ent_Name LIKE @pEnterpriseName)
				
				AND (@pWordList IS NULL 
					OR (H.FileReferenceID IS NOT NULL
					OR EXISTS (
						SELECT 
							DISTINCT
							FRLX.FileReferenceID
						FROM
							esf_files.FileReferenceLevel FRLX WITH (NOLOCK)
						INNER JOIN	
							@searchWords S ON FRLX.Name LIKE '%' + S.searchItem + '%'
						WHERE 
							FRLX.FileReferenceID = FR.ID)
					OR EXISTS (
						SELECT 
							DISTINCT
							FRX.ID
						FROM
							esf_files.FileReference FRX WITH (NOLOCK)
						INNER JOIN
							esf_sso.VEnterprise EX ON EX.Ent_ID = FRX.EnterpriseID
						INNER JOIN	
							@searchWords S ON 
								EX.Ent_Name LIKE '%' + S.searchItem + '%'
								OR EX.Ent_IdentificationNumber LIKE '%' + S.searchItem + '%'
								OR EX.Ent_Code LIKE '%' + S.searchItem + '%'
						WHERE 
							FRX.ID = FR.ID)
					OR EXISTS (
						SELECT 
							DISTINCT
							FRX.ID
						FROM
							esf_files.FileReference FRX WITH (NOLOCK)
						INNER JOIN	
							@searchWords S ON 
								FRX.OnlyName LIKE '%' + S.searchItem + '%'
								OR FRX.Description LIKE '%' + S.searchItem + '%'
						WHERE 
							FRX.ID = FR.ID)
					)
				)
				
				AND (@pXMLMultiLevelsSearch IS NULL 
				OR EXISTS
				(
					SELECT 
						DISTINCT 
						FRLX.FileReferenceID
					FROM
						esf_files.FileReferenceLevel FRLX WITH (NOLOCK)
					INNER JOIN
						@XMLMultiLevelsSearchTable MLS 
					ON 
						MLS.Level = FRLX.Level
						AND MLS.Name = FRLX.Name
						AND FRLX.FileReferenceID = FR.ID
					GROUP BY
						FRLX.FileReferenceID
					HAVING 
						COUNT (FRLX.FileReferenceID) = @XMLMultiLevelsSearchCount
				))
									
			) X
			
		) P	
	WHERE
		P.Ri BETWEEN (@pPageIndex * @pPageSize + 1) AND (@pPageIndex + 1) * @pPageSize
	
	
	SELECT 
		PFR.ID
		,PFR.ImageFormat
		,PFR.Description
		,PFR.DownloadCount
		,PFR.CreationDateTime
		,PFR.ItemsCount
		,PFR.LastWriteDateTime
		,PFR.LengthBytes
		,PFR.MonitoredDirectoryID
		,PFR.MonitoredDirectorySubCategoryID
		,PFR.OnlyExtension
		,PFR.OnlyName
		,PFR.OpenCount
		,PFR.ParentFileReferenceID
		,PFR.URL
		,PFR.LastUpdateAction
		,PFR.URLMD5
		,PFR.VersionDateTime
		
		,(SELECT
			E.Ent_Name	Name
			,E.Ent_IdentificationNumber IdentificationNumber
			,E.Ent_IdentificationNumberDigit IdentificationNumberDigit
			,E.Ent_IdType_Code IdentificationTypeCode
		FROM
			esf_sso.VEnterprise E WITH (NOLOCK) 
		WHERE
			E.Ent_ID = PFR.EnterpriseID
		FOR XML AUTO, TYPE) PEs
		
		,(SELECT 
			FRL.Level
			,FRL.Name
		FROM 
			esf_files.FileReferenceLevel FRL WITH (NOLOCK)
		WHERE
			FRL.FileReferenceID = PFR.ID
			
		FOR XML AUTO, TYPE) PFRLs
		
		,(SELECT
			H.PageIndex
			,H.WordSearchName
			,H.X0
			,H.Y0
			,H.X1
			,H.Y1	
		FROM
			@hits H
		WHERE
			H.FileReferenceID = PFR.ID
		FOR XML AUTO, TYPE) PHs
		
		,(SELECT
			FR.ID
			,FR.ImageFormat
			,FR.Description
			,FR.DownloadCount
			,FR.CreationDateTime
			,FR.ItemsCount
			,FR.LastWriteDateTime
			,FR.LengthBytes
			,FR.MonitoredDirectoryID
			,FR.MonitoredDirectorySubCategoryID
			,FR.OnlyExtension
			,FR.OnlyName
			,FR.OpenCount
			,FR.ParentFileReferenceID
			,FR.URL
			,FR.LastUpdateAction
			,FR.URLMD5
			,FR.VersionDateTime
			
			,(SELECT
				H.PageIndex
				,H.WordSearchName
				,H.X0
				,H.Y0
				,H.X1
				,H.Y1	
			FROM
				@hits H
			WHERE
				H.FileReferenceID = FR.ID
			FOR XML AUTO, TYPE) Hs
			
		FROM
			esf_files.FileReference FR WITH (NOLOCK)
		WHERE
			FR.ParentFileReferenceID = PFR.ID
		FOR XML AUTO, TYPE) CFRs
		
		,P.RCount
		,P.Ri
	FROM
		@PageVectorTable P
	LEFT JOIN
		esf_files.FileReference PFR ON PFR.ID = P.ParentFileReferenceID
	FOR XML AUTO
	
	